package com.my.xq.repository;



import java.util.Date;
import java.util.List;

import javax.transaction.Transactional;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import com.my.xq.domain.entity.RenWuCreate;
import com.sskj.core.repository.GenericRepository;

public interface RenWuCreateRepository extends GenericRepository<RenWuCreate, String> {

	
	@Query(value = "from RenWuCreate o where o.rwCode=?1")
	RenWuCreate findOneByCode(String rwCode);	
	
	
	@Query("from  RenWuCreate  o  where o.courtId = ?1 and o.rwCode=?2 and o.building=?3 and o.unit=?4 and o.isOver=false ")
	public RenWuCreate findRenWuByUnit(String courtId,String rwCode,String building,String unit);	
	
	@Query("from  RenWuCreate  o  where o.courtId = ?1 and o.rwCode=?2 and o.building=?3 and o.isOver=false ")
	public RenWuCreate findRenWuBybuilding(String courtId,String rwCode,String building);	
	
	@Query("from  RenWuCreate  o  where o.courtId = ?1 and o.rwCode=?2  and o.isOver=false ")
	public RenWuCreate findRenWuByCode(String courtId,String rwCode);

	@Query(value = 
		" SELECT rw.* "
		+ " FROM "
		+ "   sys_ren_wu_create rw,"
		+ "   (SELECT "
		+ "    DISTINCT building,unit "
		+ "   FROM xq_doorplate d "
		+ "   WHERE "
		+ "    (d.court_id=?1 AND d.proprietor_id=?2)"
		+ "    OR"
		+ "    (d.id IN (SELECT a.doorplate_id FROM xq_proprietor_auth a,xq_doorplate d1 WHERE a.proprietor_id=?2 AND d1.id=a.doorplate_id AND d1.court_id=?1 AND a.auth=1))) "
		+ "   AS door "
		+ " WHERE "
		+ "   rw.court_id=?1 "
		+ "  AND "
		+ "   rw.rw_code='CAST01' "
		+ "  AND "
		+ "   rw.building=door.building "
		+ "  AND "
		+ "   rw.unit=door.unit "
		+ "  AND "
		+ "   rw.is_active=1 "
		+ "  ORDER BY rw.building ASC,rw.unit ASC", nativeQuery = true)
	List<RenWuCreate> findRenWuCAST01LsByCourt(String courtId, String proprietorId);

	@Query(value = 
		" SELECT rw.* "
		+ " FROM "
		+ "   sys_ren_wu_create rw,"
		+ "   (SELECT "
		+ "    DISTINCT building "
		+ "   FROM xq_doorplate d "
		+ "   WHERE "
		+ "    (d.court_id=?1 AND d.proprietor_id=?2)"
		+ "    OR"
		+ "    (d.id IN (SELECT a.doorplate_id FROM xq_proprietor_auth a,xq_doorplate d1 WHERE a.proprietor_id=?2 AND d1.id=a.doorplate_id AND d1.court_id=?1 AND a.auth=1))) "
		+ "   AS door "
		+ " WHERE "
		+ "   rw.court_id=?1 "
		+ "  AND "
		+ "   rw.rw_code= 'CAST02' "
		+ "  AND "
		+ "   rw.building=door.building "
		+ "  AND "
		+ "   rw.is_active=1 "
		+ "  ORDER BY rw.building ASC,rw.unit ASC", nativeQuery = true)
	List<RenWuCreate> findRenWuCAST02LsByCourt(String courtId, String proprietorId);

	@Query(value = 
		" SELECT rw.* "
		+ " FROM "
		+ "   sys_ren_wu_create rw "
		+ " WHERE "
		+ "   rw.court_id=?1 "
		+ "  AND "
		+ "   rw.rw_code= 'CAST03' "
		+ "  AND "
		+ "   rw.is_active=1 "
		+ "  ORDER BY rw.building ASC,rw.unit ASC", nativeQuery = true)
	List<RenWuCreate> findRenWuCAST03LsByCourt(String courtId);

	@Query(value = 
		" SELECT rw.* "
		+ " FROM "
		+ "   sys_ren_wu_create rw "
		+ " WHERE "
		+ "   rw.court_id=?1 "
		+ "  AND "
		+ "   rw.rw_code= 'CAST04' "
		+ "  AND "
		+ "   rw.is_active=1 "
		+ "  ORDER BY rw.building ASC,rw.unit ASC", nativeQuery = true)
	List<RenWuCreate> findRenWuCAST04LsByCourt(String courtId);

	@Query(value = "select count(c.id) from sys_ren_wu_create c where c.rw_code=?1 and "
			+ "c.court_id=?2 and c.building=?3 and c.unit=?4", nativeQuery = true) 
	Integer findCastCount(String rwCode,String courtId,String building,String unit);	
	
	@Query(value = "select count(c.id) from sys_ren_wu_create c where c.rw_code=?1 and "
			+ "c.court_id=?2 and c.building=?3 ", nativeQuery = true) 
	Integer findCastCount(String rwCode,String courtId,String building);
	
	@Query(value = "select count(c.id) from sys_ren_wu_create c where c.rw_code=?1 and "
			+ "c.court_id=?2 ", nativeQuery = true) 
	Integer findCastCount(String rwCode,String courtId);	
	
	@Transactional
	@Modifying(clearAutomatically = true)
	@Query("update RenWuCreate o set o.courtId=?1 where o.rwCode=?2")
	void updateRenWu(String courtId,String code);		

	@Transactional
	@Modifying(clearAutomatically = true)
	@Query("update RenWuCreate o set o.isActive=0,o.isOver=1"
			+ " where o.courtId=?1 and o.rwCode=?2")
	void closeRenWuCreate(String courtId,String code);

	@Transactional
	@Modifying(clearAutomatically = true)
	@Query("update RenWuCreate o set o.isActive=0,o.isOver=1"
			+ " where o.courtId=?1 and o.rwCode=?2 and o.building = ?3 and o.unit = ?4 ")
	void closeRenWuCreateCAST01(String courtId,String code, String building, String unit);

	@Transactional
	@Modifying(clearAutomatically = true)
	@Query("update RenWuCreate o set o.isActive=0,o.isOver=1"
			+ " where o.courtId=?1 and o.rwCode=?2 and o.building = ?3 ")
	void closeRenWuCreateCAST02(String courtId,String code, String building);

	@Transactional
	@Modifying(clearAutomatically = true)
	@Query("update RenWuCreate o set o.isActive=0,o.isOver=1"
			+ " where o.courtId=?1 and o.rwCode=?2 ")
	void closeRenWuCreateCAST03(String courtId,String code);

	@Transactional
	@Modifying(clearAutomatically = true)
	@Query("update RenWuCreate o set o.isActive=1,o.isOver=0,o.startTime=?1,o.endTime=?2"
			+ " where o.courtId=?3 and o.rwCode=?4")
	void openRenWuCreate(Date StartTime,Date EndTime,String courtId,String code);
	
	@Query(value = "delete from RenWuCreate where courtId =?1 ")
	void delByCourtId(String courtId);
	
}
